<?php

namespace App\Http\Controllers\Admin;

use Illuminate\Http\Request;
use App\Http\Requests;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Input;
use Cache,Event,Auth;

class MjphController extends Controller
{
	 public function index(request $request){
		 //警员自己排行
		 if($request->isMethod('post')){
			 $uid['id'] = Auth::guard('admin')->user()->id;
			 $user = db::table('admin_users')->where($uid)->first();

			 if($request->type == 0){

			 	if ($request->times == 'zhou') {
					 $nt = '(DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(jiancha.created_at))';
					 $ryshnt = '(DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(rysh.clsj))';
					 $jqnt = '(DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(jqsb.czsj))';
					 $yjnt = '(DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(yjfk.clsj))';
				 }else if ($request->times == 'yue'){
					 $nt = '(DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(jiancha.created_at))';
					 $ryshnt = '(DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(rysh.clsj))';
					 $jqnt = '(DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(jqsb.czsj))';
					 $yjnt = '(DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(yjfk.clsj))';
				 }else if ($request->times) {
					 $nt = '(DATE_SUB(CURDATE(), INTERVAL 365 DAY) <= date(jiancha.created_at))';
					 $ryshnt = '(DATE_SUB(CURDATE(), INTERVAL 365 DAY) <= date(rysh.clsj))';
					 $jqnt = '(DATE_SUB(CURDATE(), INTERVAL 365 DAY) <= date(jqsb.czsj))';
					 $yjnt = '(DATE_SUB(CURDATE(), INTERVAL 365 DAY) <= date(yjfk.clsj))';
				 }


		
				if ($request->city == 0){
					
						if ($user->dengji == 1) {
						 	$ar = '';
						}

						if ($user->dengji == 2) {
							$area = $user->city;
							$ar = 'us.city'.'='.$area.' '.'AND'.' ';
						}

						if ($user->dengji == 3) {
							$area = $user->area;
							$ar = 'us.area'.'='.$area.' '.'AND'.' ';
						}

						if ($user->dengji == 4) {
							$area = $user->org_id;
							$ar = 'us.org_id'.'='.$area.' '.'AND'.' ';
						}
				}else{


						$q['id'] = $request->city;
						$qq = db::table('area')->where($q)->first();
						$area = $request->city;

						if ($qq->dengji == 1) {
							$ar = 'us.city'.'='.$area.' '.'AND'.' ';
						}

						if ($qq->dengji == 2) {
							$ar = 'us.area'.'='.$area.' '.'AND'.' ';
						}

						if ($qq->dengji == 3) {
							$ar = 'us.org_id'.'='.$area.' '.'AND'.' ';
						}


					}

					//删除
					DB::table('qymjph')->delete();

					//检查
					$jc =  DB::select("SELECT  jiancha.user_id, us.name ,count(1)as count FROM `jiancha`  LEFT JOIN admin_users as us ON us.id = jiancha.user_id  WHERE  $ar $nt GROUP BY user_id,us.name ORDER BY count DESC");

					foreach ($jc as $key => $value) {
						$ry['user_id'] = $value->user_id;
						$ry['newcount'] = $value->count;
						DB::table('qymjph')->insert($ry);
					}

					//人员审核
					$rysh = DB::select("SELECT  rysh.police_id, us.name, count(1)as count FROM `rysh`  LEFT JOIN admin_users as us ON us.id = rysh.police_id  WHERE  $ar $ryshnt AND rysh.police_cl = 1 GROUP BY police_id,us.name ORDER BY count DESC");

					foreach ($rysh as $key => $value) {
						$j['user_id'] = $value->police_id;
						$jq['newcount'] = $value->count;
						$fj = DB::table('qymjph')->where($j)->first();
						if ($fj) {
							DB::table('qymjph')->where($j)->increment('newcount',$value->count);
							}else{
							$jq['user_id'] = $value->police_id;
							DB::table('qymjph')->insert($jq);
						}
					}



					//警情上报
					$jqsb =  DB::select("SELECT  jqsb.police_id, us.name, count(1)as count FROM `jqsb`  LEFT JOIN admin_users as us ON us.id = jqsb.police_id  WHERE  $ar $jqnt AND jqsb.police_cl = 1 GROUP BY police_id,us.name ORDER BY count DESC");


					foreach ($jqsb as $key => $value) {
						$j['user_id'] = $value->police_id;
						$jq['newcount'] = $value->count;
						$fj = DB::table('qymjph')->where($j)->first();
						if ($fj) {
							DB::table('qymjph')->where($j)->increment('newcount',$value->count);
							}else{
							$jq['user_id'] = $value->police_id;
							DB::table('qymjph')->insert($jq);
						}
					}



					//意见反馈
					$yjfk =  DB::select("SELECT  yjfk.police_id, us.name, count(1)as count FROM `yjfk`  LEFT JOIN admin_users as us ON us.id = yjfk.police_id  WHERE  $ar $yjnt AND yjfk.police_cl = 1 GROUP BY police_id,us.name ORDER BY count DESC");

					foreach ($yjfk as $key => $value) {
						$j['user_id'] = $value->police_id;
						$jq['newcount'] = $value->count;
						$fj = DB::table('qymjph')->where($j)->first();
						if ($fj) {
							DB::table('qymjph')->where($j)->increment('newcount',$value->count);
						}else{
							$jq['user_id'] = $value->police_id;
							DB::table('qymjph')->insert($jq);
						}
					}

					$re['jc_num'] = DB::select("SELECT u.count,u.user_id,u.name,(CASE WHEN @rowtotal = u.count THEN @rownum WHEN @rowtotal := u.count THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN @rownum :=@rownum + 1 END) as rank FROM (SELECT  qymjph.user_id, us.name ,qymjph.newcount as count FROM `qymjph`  LEFT JOIN admin_users as us ON us.id = qymjph.user_id   ORDER BY count DESC) AS u,(SELECT @rownum := 0 ,@rowtotal := NULL) r");





			 }

			 if($request->type == 1){
				 //获取检查排行榜

				 if ($request->times == 'zhou') {
					 $nt = '(DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(jiancha.created_at))';
				 }else if ($request->times == 'yue'){
					 $nt = '(DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(jiancha.created_at))';
				 }else if ($request->times) {
					 $nt = '(DATE_SUB(CURDATE(), INTERVAL 365 DAY) <= date(jiancha.created_at))';
				 }


		
				if ($request->city == 0){
					
						if ($user->dengji == 1) {
						 	$ar = '';
						}

						if ($user->dengji == 2) {
							$area = $user->city;
							$ar = 'us.city'.'='.$area.' '.'AND'.' ';
						}

						if ($user->dengji == 3) {
							$area = $user->area;
							$ar = 'us.area'.'='.$area.' '.'AND'.' ';
						}

						if ($user->dengji == 4) {
							$area = $user->org_id;
							$ar = 'us.org_id'.'='.$area.' '.'AND'.' ';
						}
				}else{


						$q['id'] = $request->city;
						$qq = db::table('area')->where($q)->first();
						$area = $request->city;

						if ($qq->dengji == 1) {
							$ar = 'us.city'.'='.$area.' '.'AND'.' ';
						}

						if ($qq->dengji == 2) {
							$ar = 'us.area'.'='.$area.' '.'AND'.' ';
						}

						if ($qq->dengji == 3) {
							$ar = 'us.org_id'.'='.$area.' '.'AND'.' ';
						}


					}
				
				



				 
				 // if ($request->city == 'suo') {
					//  $area =  $user->org_id;
					//  $ar = 'us.org_id'.'='.$area.' '.'AND'.' ';
				 // }elseif ($request->city == 'qu') {
					//  $area =  $user->area;
					//  $ar = 'us.area'.'='.$area.' '.'AND'.' ';
				 // }elseif($request->city == 'shi'){
					//  $area =  $user->city;
					//  $ar = 'us.city'.'='.$area.' '.'AND'.' ';
				 // }elseif ($request->city == 'sheng') {
					//  $ar = '';
				 // }

				 // SELECT u.count,u.user_id,u.name,(CASE WHEN @rowtotal = u.count THEN @rownum WHEN @rowtotal := u.count THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN  @rownum :=@rownum + 1 END) as rank FROM(SELECT  jiancha.user_id, us.name ,count(1)as count FROM `jiancha`  LEFT JOIN admin_users as us ON us.id = jiancha.user_id  WHERE  $ar $nt  GROUP BY user_id,us.name ORDER BY count DESC) AS u,(SELECT @rownum := 0 ,@rowtotal := NULL) r

				 $re['jc_num'] = DB::select("SELECT u.count,u.user_id,u.name,(CASE WHEN @rowtotal = u.count THEN @rownum WHEN @rowtotal := u.count THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN  @rownum :=@rownum + 1 END) as rank FROM( SELECT  jiancha.user_id, us.name ,count(1)as count FROM `jiancha`  LEFT JOIN admin_users as us ON us.id = jiancha.user_id  WHERE  $ar $nt GROUP BY user_id,us.name ORDER BY count DESC) AS u,(SELECT @rownum := 0 ,@rowtotal := NULL) r");
			 }

			 if ($request->type == 2) {


				 if ($request->times == 'zhou') {
					 $nt = '(DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(rysh.clsj))';
				 }else if ($request->times == 'yue'){
					 $nt = '(DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(rysh.clsj))';
				 }else if ($request->times) {
					 $nt = '(DATE_SUB(CURDATE(), INTERVAL 365 DAY) <= date(rysh.clsj))';
				 }


				if ($request->city == 0){
					
						if ($user->dengji == 1) {
						 	$ar = '';
						}

						if ($user->dengji == 2) {
							$area = $user->city;
							$ar = 'us.city'.'='.$area.' '.'AND'.' ';
						}

						if ($user->dengji == 3) {
							$area = $user->area;
							$ar = 'us.area'.'='.$area.' '.'AND'.' ';
						}

						if ($user->dengji == 4) {
							$area = $user->org_id;
							$ar = 'us.org_id'.'='.$area.' '.'AND'.' ';
						}
				}else{


						$q['id'] = $request->city;
						$qq = db::table('area')->where($q)->first();
						$area = $request->city;

						if ($qq->dengji == 1) {
							$ar = 'us.city'.'='.$area.' '.'AND'.' ';
						}

						if ($qq->dengji == 2) {
							$ar = 'us.area'.'='.$area.' '.'AND'.' ';
						}

						if ($qq->dengji == 3) {
							$ar = 'us.org_id'.'='.$area.' '.'AND'.' ';
						}


					}
				






				 // if ($request->city == 'suo') {
					//  $area =  $user->org_id;
					//  $ar = 'us.org_id'.'='.$area.' '.'AND'.' ';
				 // }elseif ($request->city == 'qu') {
					//  $area =  $user->area;
					//  $ar = 'us.area'.'='.$area.' '.'AND'.' ';
				 // }elseif($request->city == 'shi'){
					//  $area =  $user->city;
					//  $ar = 'us.city'.'='.$area.' '.'AND'.' ';
				 // }elseif ($request->city == 'sheng') {
					//  $ar = '';
				 // }


				 // SELECT u.count,u.police_id,u.name,(CASE WHEN @rowtotal = u.count THEN @rownum WHEN @rowtotal := u.count THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN  @rownum :=@rownum + 1 END) as rank FROM( SELECT  rysh.police_id, us.name, count(1)as count FROM `rysh`  LEFT JOIN admin_users as us ON us.id = rysh.police_id  WHERE  $ar $nt GROUP BY police_id,us.name ORDER BY count DESC) AS u,(SELECT @rownum := 0 ,@rowtotal := NULL) r

				 $re['jc_num'] = DB::select("SELECT u.count,u.police_id as user_id,u.name,(CASE WHEN @rowtotal = u.count THEN @rownum WHEN @rowtotal := u.count THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN  @rownum :=@rownum + 1 END) as rank FROM( SELECT  rysh.police_id, us.name, count(1)as count FROM `rysh`  LEFT JOIN admin_users as us ON us.id = rysh.police_id  WHERE  $ar $nt AND rysh.police_cl = 1 GROUP BY police_id,us.name ORDER BY count DESC) AS u,(SELECT @rownum := 0 ,@rowtotal := NULL) r");
			 }

			 if ($request->type == 3) {


				 if ($request->times == 'zhou') {
					 $nt = '(DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(jqsb.czsj))';
				 }else if ($request->times == 'yue'){
					 $nt = '(DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(jqsb.czsj))';
				 }else if ($request->times) {
					 $nt = '(DATE_SUB(CURDATE(), INTERVAL 365 DAY) <= date(jqsb.czsj))';
				 }


				if ($request->city == 0){
					
						if ($user->dengji == 1) {
						 	$ar = '';
						}

						if ($user->dengji == 2) {
							$area = $user->city;
							$ar = 'us.city'.'='.$area.' '.'AND'.' ';
						}

						if ($user->dengji == 3) {
							$area = $user->area;
							$ar = 'us.area'.'='.$area.' '.'AND'.' ';
						}

						if ($user->dengji == 4) {
							$area = $user->org_id;
							$ar = 'us.org_id'.'='.$area.' '.'AND'.' ';
						}
				}else{


						$q['id'] = $request->city;
						$qq = db::table('area')->where($q)->first();
						$area = $request->city;

						if ($qq->dengji == 1) {
							$ar = 'us.city'.'='.$area.' '.'AND'.' ';
						}

						if ($qq->dengji == 2) {
							$ar = 'us.area'.'='.$area.' '.'AND'.' ';
						}

						if ($qq->dengji == 3) {
							$ar = 'us.org_id'.'='.$area.' '.'AND'.' ';
						}


					}




				 // if ($request->city == 'suo') {
					//  $area =  $user->org_id;
					//  $ar = 'us.org_id'.'='.$area.' '.'AND'.' ';
				 // }elseif ($request->city == 'qu') {
					//  $area =  $user->area;
					//  $ar = 'us.area'.'='.$area.' '.'AND'.' ';
				 // }elseif($request->city == 'shi'){
					//  $area =  $user->city;
					//  $ar = 'us.city'.'='.$area.' '.'AND'.' ';
				 // }elseif ($request->city == 'sheng') {
					//  $ar = '';
				 // }

				 // SELECT u.count,u.police_id as user_id,u.name,(CASE WHEN @rowtotal = u.count THEN @rownum WHEN @rowtotal := u.count THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN  @rownum :=@rownum + 1 END) as rank FROM( SELECT  jqsb.police_id, us.name, count(1)as count FROM `jqsb`  LEFT JOIN admin_users as us ON us.id = jqsb.police_id  WHERE  $ar $nt AND jqsb.police_cl = 1 GROUP BY police_id,us.name ORDER BY count DESC) AS u,(SELECT @rownum := 0 ,@rowtotal := NULL) r

				 $re['jc_num'] = DB::select("SELECT u.count,u.police_id as user_id,u.name,(CASE WHEN @rowtotal = u.count THEN @rownum WHEN @rowtotal := u.count THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN  @rownum :=@rownum + 1 END) as rank FROM( SELECT  jqsb.police_id, us.name, count(1)as count FROM `jqsb`  LEFT JOIN admin_users as us ON us.id = jqsb.police_id  WHERE  $ar $nt AND jqsb.police_cl = 1 GROUP BY police_id,us.name ORDER BY count DESC) AS u,(SELECT @rownum := 0 ,@rowtotal := NULL) r");
			 }


			 if ($request->type == 4) {
				 if ($request->times == 'zhou') {
					 $nt = '(DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(yjfk.clsj))';
				 }else if ($request->times == 'yue'){
					 $nt = '(DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(yjfk.clsj))';
				 }else if ($request->times) {
					 $nt = '(DATE_SUB(CURDATE(), INTERVAL 365 DAY) <= date(yjfk.clsj))';
				 }

				if ($request->city == 0){
					
						if ($user->dengji == 1) {
						 	$ar = '';
						}

						if ($user->dengji == 2) {
							$area = $user->city;
							$ar = 'us.city'.'='.$area.' '.'AND'.' ';
						}

						if ($user->dengji == 3) {
							$area = $user->area;
							$ar = 'us.area'.'='.$area.' '.'AND'.' ';
						}

						if ($user->dengji == 4) {
							$area = $user->org_id;
							$ar = 'us.org_id'.'='.$area.' '.'AND'.' ';
						}
				}else{


						$q['id'] = $request->city;
						$qq = db::table('area')->where($q)->first();
						$area = $request->city;

						if ($qq->dengji == 1) {
							$ar = 'us.city'.'='.$area.' '.'AND'.' ';
						}

						if ($qq->dengji == 2) {
							$ar = 'us.area'.'='.$area.' '.'AND'.' ';
						}

						if ($qq->dengji == 3) {
							$ar = 'us.org_id'.'='.$area.' '.'AND'.' ';
						}


					}




				 // if ($request->city == 'suo') {
					//  $area =  $user->org_id;
					//  $ar = 'us.org_id'.'='.$area.' '.'AND'.' ';
				 // }elseif ($request->city == 'qu') {
					//  $area =  $user->area;
					//  $ar = 'us.area'.'='.$area.' '.'AND'.' ';
				 // }elseif($request->city == 'shi'){
					//  $area =  $user->city;
					//  $ar = 'us.city'.'='.$area.' '.'AND'.' ';
				 // }elseif ($request->city == 'sheng') {
					//  $ar = '';
				 // }

				 // SELECT u.count,u.police_id as user_id,u.name,(CASE WHEN @rowtotal = u.count THEN @rownum WHEN @rowtotal := u.count THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN  @rownum :=@rownum + 1 END) as rank FROM( SELECT  yjfk.police_id, us.name, count(1)as count FROM `yjfk`  LEFT JOIN admin_users as us ON us.id = yjfk.police_id  WHERE  $ar $nt AND yjfk.police_cl = 1 GROUP BY police_id,us.name ORDER BY count DESC) AS u,(SELECT @rownum := 0 ,@rowtotal := NULL) r
				 $re['jc_num'] = DB::select("SELECT u.count,u.police_id as user_id,u.name,(CASE WHEN @rowtotal = u.count THEN @rownum WHEN @rowtotal := u.count THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN  @rownum :=@rownum + 1 END) as rank FROM( SELECT  yjfk.police_id, us.name, count(1)as count FROM `yjfk`  LEFT JOIN admin_users as us ON us.id = yjfk.police_id  WHERE  $ar $nt AND yjfk.police_cl = 1 GROUP BY police_id,us.name ORDER BY count DESC) AS u,(SELECT @rownum := 0 ,@rowtotal := NULL) r");
			 }

			 if($re){
				 $result = array('status' => '1000',  'message' => $re);
				 echo json_encode($result,TRUE);
				 exit();
			 }else{
				 $result = array('status' => '1001',  'message' => '暂无数据');
				 echo json_encode($result,TRUE);
				 exit();
			 }
		 }
 	 	
        return view('admin.mjph.index');

    }

    public function qiye(request $request){


    	if($request->isMethod('post')){
	 		// $userid['id'] = Auth::guard('admin')->user()->unit_id;
		 	// $a = DB::table('units')->where($userid)->first();
		 	 $uid['id'] = Auth::guard('admin')->user()->id;
		 	 $user = db::table('admin_users')->where($uid)->first();

	 		
	 		//综合排行
		 	  if ($request->type == 0) {

		 	 	if ($request->times == 'zhou') {
	                $nt = '(DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(rysh.sbsj))';
	                $jqnt = '(DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(jqsb.sbsj))';
	                $xsnt = '(DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(xiansuo.jbsj))';
	               	$yjnt = '(DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(yjfk.jbsj))';
	            }else if ($request->times == 'yue'){
	                $nt = '(DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(rysh.sbsj))';
	                $jqnt = '(DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(jqsb.sbsj))';
	                $xsnt = '(DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(xiansuo.jbsj))';
	                $yjnt = '(DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(yjfk.jbsj))';
	            }else if ($request->times == 'nian') {
	                $nt = '(DATE_SUB(CURDATE(), INTERVAL 365 DAY) <= date(rysh.sbsj))';
	                $jqnt = '(DATE_SUB(CURDATE(), INTERVAL 365 DAY) <= date(jqsb.sbsj))';
	                $xsnt = '(DATE_SUB(CURDATE(), INTERVAL 365 DAY) <= date(xiansuo.jbsj))';
	                $yjnt = '(DATE_SUB(CURDATE(), INTERVAL 365 DAY) <= date(yjfk.jbsj))';
	            }


	        	if ($request->city == 0){
						if ($user->dengji == 1) {
						 	$ar = '';
						}

						if ($user->dengji == 2) {
							$area = $user->city;
							$ar = 'un.city'.'='.$area.' '.'AND'.' ';
						}

						if ($user->dengji == 3) {
							$area = $user->area;
							$ar = 'un.area'.'='.$area.' '.'AND'.' ';
						}

						if ($user->dengji == 4) {
							$area = $user->org_id;
							$ar = 'un.org_id'.'='.$area.' '.'AND'.' ';
						}
				}else{
						$q['id'] = $request->city;
						$qq = db::table('area')->where($q)->first();
						$area = $request->city;

						if ($qq->dengji == 1) {
							$ar = 'un.city'.'='.$area.' '.'AND'.' ';
						}

						if ($qq->dengji == 2) {
							$ar = 'un.area'.'='.$area.' '.'AND'.' ';
						}

						if ($qq->dengji == 3) {
							$ar = 'un.org_id'.'='.$area.' '.'AND'.' ';
						}
					}

					//清空表qyzhph
					DB::table('qyzhph')->delete();

					//获取人员审核的数量添加
					$rysh = DB::select("SELECT rysh.unit_id, un.name ,count(1)as count FROM `rysh`  LEFT JOIN units as un ON un.id = rysh.unit_id  WHERE  $ar $nt  GROUP BY rysh.unit_id,un.name ORDER BY count DESC");

					foreach ($rysh as $key => $value) {
						$ry['unit_id'] = $value->unit_id;
						$ry['newcount'] = $value->count;
						DB::table('qyzhph')->insert($ry);
					}

					//警情上报
					$jqsb = DB::select("SELECT jqsb.unit_id, un.name ,count(1)as count FROM `jqsb`  LEFT JOIN units as un ON un.id = jqsb.unit_id  WHERE  $ar $jqnt  GROUP BY jqsb.unit_id,un.name ORDER BY count DESC");

					foreach ($jqsb as $key => $value) {
						$j['unit_id'] = $value->unit_id;
						$jq['newcount'] = $value->count;
						$fj = DB::table('qyzhph')->where($j)->first();
						if ($fj) {
							DB::table('qyzhph')->where($j)->increment('newcount',$value->count);
							}else{
							$jq['unit_id'] = $value->unit_id;
							DB::table('qyzhph')->insert($jq);
						}
					}


					//线索举报
					$xsjb = DB::select("SELECT xiansuo.unit_id, un.name ,count(1)as count FROM `xiansuo`  LEFT JOIN units as un ON un.id = xiansuo.unit_id  WHERE  $ar $xsnt  GROUP BY xiansuo.unit_id,un.name ORDER BY count DESC");

					foreach ($xsjb as $key => $value) {
						$j['unit_id'] = $value->unit_id;
						$jq['newcount'] = $value->count;
						$fj = DB::table('qyzhph')->where($j)->first();
						if ($fj) {
							DB::table('qyzhph')->where($j)->increment('newcount',$value->count);
							}else{
							$jq['unit_id'] = $value->unit_id;
							DB::table('qyzhph')->insert($jq);
						}
					}

					//一键反馈

					$yjfk = DB::select("SELECT yjfk.unit_id, un.name ,count(1)as count FROM `yjfk`  LEFT JOIN units as un ON un.id = yjfk.unit_id  WHERE  $ar $yjnt  GROUP BY yjfk.unit_id,un.name ORDER BY count DESC");

					foreach ($yjfk as $key => $value) {
						$j['unit_id'] = $value->unit_id;
						$jq['newcount'] = $value->count;
						$fj = DB::table('qyzhph')->where($j)->first();
						if ($fj) {
							DB::table('qyzhph')->where($j)->increment('newcount',$value->count);
							}else{
							$jq['unit_id'] = $value->unit_id;
							DB::table('qyzhph')->insert($jq);
						}
					}

					$re['rank'] = DB::select("SELECT u.count,u.unit_id,u.name,(CASE WHEN @rowtotal = u.count THEN @rownum WHEN @rowtotal := u.count THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN @rownum :=@rownum + 1 END) as rank FROM (SELECT qyzhph.unit_id, un.name ,qyzhph.newcount as count FROM `qyzhph` LEFT JOIN units as un ON un.id = qyzhph.unit_id ORDER BY newcount DESC) AS u,(SELECT @rownum := 0 ,@rowtotal := NULL) r");

				 	// $re['rank'] =  DB::select("SELECT u.count,u.unit_id,u.name,(CASE WHEN @rowtotal = u.count THEN @rownum WHEN @rowtotal := u.count THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN  @rownum :=@rownum + 1 END) as rank FROM(SELECT rysh.unit_id, un.name ,count(1)as count FROM `rysh`  LEFT JOIN units as un ON un.id = rysh.unit_id  WHERE  $ar $nt  GROUP BY rysh.unit_id,un.name ORDER BY count DESC) AS u,(SELECT @rownum := 0 ,@rowtotal := NULL) r");
		 	 }

	   	
	   	 	//默认所内,本周内,人员审核,人员核查
	   	 	if ( $request->type == 1) {
	   	 		
		   	 	if ($request->times == 'zhou') {
	                $nt = '(DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(rysh.sbsj))';
	            }else if ($request->times == 'yue'){
	                $nt = '(DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(rysh.sbsj))';
	            }else if ($request->times == 'nian') {
	                $nt = '(DATE_SUB(CURDATE(), INTERVAL 365 DAY) <= date(rysh.sbsj))';
	            }

	        	if ($request->city == 0){
					
						if ($user->dengji == 1) {
						 	$ar = '';
						}

						if ($user->dengji == 2) {
							$area = $user->city;
							$ar = 'un.city'.'='.$area.' '.'AND'.' ';
						}

						if ($user->dengji == 3) {
							$area = $user->area;
							$ar = 'un.area'.'='.$area.' '.'AND'.' ';
						}

						if ($user->dengji == 4) {
							$area = $user->org_id;
							$ar = 'un.org_id'.'='.$area.' '.'AND'.' ';
						}
				}else{


						$q['id'] = $request->city;
						$qq = db::table('area')->where($q)->first();
						$area = $request->city;

						if ($qq->dengji == 1) {
							$ar = 'un.city'.'='.$area.' '.'AND'.' ';
						}

						if ($qq->dengji == 2) {
							$ar = 'un.area'.'='.$area.' '.'AND'.' ';
						}

						if ($qq->dengji == 3) {
							$ar = 'un.org_id'.'='.$area.' '.'AND'.' ';
						}


					}




	            // if ($request->city == 'suo') {
	            //     $area =  $a->org_id;
	            //     $ar = 'un.org_id'.'='.$area.' '.'AND'.' ';
	            // }elseif ($request->city == 'qu') {
	            //     $area =  $a->area;
	            //     $ar = 'un.area'.'='.$area.' '.'AND'.' ';
	            // }elseif($request->city == 'shi'){
	            //     $area =  $a->city;
	            //     $ar = 'un.city'.'='.$area.' '.'AND'.' ';
	            // }elseif($request->city == 'shen'){
	            // 	$ar = '';
	            // }


	          	$re['rank'] =  DB::select("SELECT u.count,u.unit_id,u.name,(CASE WHEN @rowtotal = u.count THEN @rownum WHEN @rowtotal := u.count THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN  @rownum :=@rownum + 1 END) as rank FROM(SELECT rysh.unit_id, un.name ,count(1)as count FROM `rysh`  LEFT JOIN units as un ON un.id = rysh.unit_id  WHERE  $ar $nt  GROUP BY rysh.unit_id,un.name ORDER BY count DESC) AS u,(SELECT @rownum := 0 ,@rowtotal := NULL) r");
	   	 	}



	   	 	//默认所内,本周内,人员审核,警情上报
	   	 	if (  $request->type == 2) {
	   	 		
		   	 	if ($request->times == 'zhou') {
	                $nt = '(DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(jqsb.sbsj))';
	            }else if ($request->times == 'yue'){
	                $nt = '(DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(jqsb.sbsj))';
	            }else if ($request->times == 'nian') {
	                $nt = '(DATE_SUB(CURDATE(), INTERVAL 365 DAY) <= date(jqsb.sbsj))';
	            }



	        	if ($request->city == 0){
					
						if ($user->dengji == 1) {
						 	$ar = '';
						}

						if ($user->dengji == 2) {
							$area = $user->city;
							$ar = 'un.city'.'='.$area.' '.'AND'.' ';
						}

						if ($user->dengji == 3) {
							$area = $user->area;
							$ar = 'un.area'.'='.$area.' '.'AND'.' ';
						}

						if ($user->dengji == 4) {
							$area = $user->org_id;
							$ar = 'un.org_id'.'='.$area.' '.'AND'.' ';
						}
				}else{


						$q['id'] = $request->city;
						$qq = db::table('area')->where($q)->first();
						$area = $request->city;

						if ($qq->dengji == 1) {
							$ar = 'un.city'.'='.$area.' '.'AND'.' ';
						}

						if ($qq->dengji == 2) {
							$ar = 'un.area'.'='.$area.' '.'AND'.' ';
						}

						if ($qq->dengji == 3) {
							$ar = 'un.org_id'.'='.$area.' '.'AND'.' ';
						}


					}



	            // if ($request->city == 'suo') {
	            //     $area =  $a->org_id;
	            //     $ar = 'un.org_id'.'='.$area.' '.'AND'.' ';
	            // }elseif ($request->city == 'qu') {
	            //     $area =  $a->area;
	            //     $ar = 'un.area'.'='.$area.' '.'AND'.' ';
	            // }elseif($request->city == 'shi'){
	            //     $area =  $a->city;
	            //     $ar = 'un.city'.'='.$area.' '.'AND'.' ';
	            // }elseif($request->city == 'shen'){
	            // 	$ar = '';
	            // }


	            


	          	$re['rank'] =  DB::select("SELECT u.count,u.unit_id,u.name,(CASE WHEN @rowtotal = u.count THEN @rownum WHEN @rowtotal := u.count THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN  @rownum :=@rownum + 1 END) as rank FROM(SELECT jqsb.unit_id, un.name ,count(1)as count FROM `jqsb`  LEFT JOIN units as un ON un.id = jqsb.unit_id  WHERE  $ar $nt  GROUP BY jqsb.unit_id,un.name ORDER BY count DESC) AS u,(SELECT @rownum := 0 ,@rowtotal := NULL) r");
	   	 	}


	   	 	//默认所内,本周内,人员审核,线索举报
	   	 	if ( $request->type == 3) {
	   	 		
		   	 	if ($request->times == 'zhou') {
	                $nt = '(DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(xiansuo.jbsj))';
	            }else if ($request->times == 'yue'){
	                $nt = '(DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(xiansuo.jbsj))';
	            }else if ($request->times == 'nian') {
	                $nt = '(DATE_SUB(CURDATE(), INTERVAL 365 DAY) <= date(xiansuo.jbsj))';
	            }
	            


	        	if ($request->city == 0){
					
						if ($user->dengji == 1) {
						 	$ar = '';
						}

						if ($user->dengji == 2) {
							$area = $user->city;
							$ar = 'un.city'.'='.$area.' '.'AND'.' ';
						}

						if ($user->dengji == 3) {
							$area = $user->area;
							$ar = 'un.area'.'='.$area.' '.'AND'.' ';
						}

						if ($user->dengji == 4) {
							$area = $user->org_id;
							$ar = 'un.org_id'.'='.$area.' '.'AND'.' ';
						}
				}else{


						$q['id'] = $request->city;
						$qq = db::table('area')->where($q)->first();
						$area = $request->city;

						if ($qq->dengji == 1) {
							$ar = 'un.city'.'='.$area.' '.'AND'.' ';
						}

						if ($qq->dengji == 2) {
							$ar = 'un.area'.'='.$area.' '.'AND'.' ';
						}

						if ($qq->dengji == 3) {
							$ar = 'un.org_id'.'='.$area.' '.'AND'.' ';
						}


					}


	            // if ($request->city == 'suo') {
	            //     $area =  $a->org_id;
	            //     $ar = 'un.org_id'.'='.$area.' '.'AND'.' ';
	            // }elseif ($request->city == 'qu') {
	            //     $area =  $a->area;
	            //     $ar = 'un.area'.'='.$area.' '.'AND'.' ';
	            // }elseif($request->city == 'shi'){
	            //     $area =  $a->city;
	            //     $ar = 'un.city'.'='.$area.' '.'AND'.' ';
	            // }elseif($request->city == 'shen'){
	            // 	$ar = '';
	            // }


	         



	          	$re['rank'] =  DB::select("SELECT u.count,u.unit_id,u.name,(CASE WHEN @rowtotal = u.count THEN @rownum WHEN @rowtotal := u.count THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN  @rownum :=@rownum + 1 END) as rank FROM(SELECT xiansuo.unit_id, un.name ,count(1)as count FROM `xiansuo`  LEFT JOIN units as un ON un.id = xiansuo.unit_id  WHERE  $ar $nt  GROUP BY xiansuo.unit_id,un.name ORDER BY count DESC) AS u,(SELECT @rownum := 0 ,@rowtotal := NULL) r");
	   	 	}


	   	 	//默认所内,本周内,人员审核,线索举报
	   	 	if ( $request->type == 4) {
	   	 		
		   	 	if ($request->times == 'zhou') {
	                $nt = '(DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(yjfk.jbsj))';
	            }else if ($request->times == 'yue'){
	                $nt = '(DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(yjfk.jbsj))';
	            }else if ($request->times == 'nian') {
	                $nt = '(DATE_SUB(CURDATE(), INTERVAL 365 DAY) <= date(yjfk.jbsj))';
	            }



	        	if ($request->city == 0){
					
						if ($user->dengji == 1) {
						 	$ar = '';
						}

						if ($user->dengji == 2) {
							$area = $user->city;
							$ar = 'un.city'.'='.$area.' '.'AND'.' ';
						}

						if ($user->dengji == 3) {
							$area = $user->area;
							$ar = 'un.area'.'='.$area.' '.'AND'.' ';
						}

						if ($user->dengji == 4) {
							$area = $user->org_id;
							$ar = 'un.org_id'.'='.$area.' '.'AND'.' ';
						}
				}else{


						$q['id'] = $request->city;
						$qq = db::table('area')->where($q)->first();
						$area = $request->city;

						if ($qq->dengji == 1) {
							$ar = 'un.city'.'='.$area.' '.'AND'.' ';
						}

						if ($qq->dengji == 2) {
							$ar = 'un.area'.'='.$area.' '.'AND'.' ';
						}

						if ($qq->dengji == 3) {
							$ar = 'un.org_id'.'='.$area.' '.'AND'.' ';
						}


					}



	            // if ($request->city == 'suo') {
	            //     $area =  $a->org_id;
	            //     $ar = 'un.org_id'.'='.$area.' '.'AND'.' ';
	            // }elseif ($request->city == 'qu') {
	            //     $area =  $a->area;
	            //     $ar = 'un.area'.'='.$area.' '.'AND'.' ';
	            // }elseif($request->city == 'shi'){
	            //     $area =  $a->city;
	            //     $ar = 'un.city'.'='.$area.' '.'AND'.' ';
	            // }elseif($request->city == 'shen'){
	            // 	$ar = '';
	            // }


	              // SELECT u.count,u.unit_id,u.name,(CASE WHEN @rowtotal = u.unit_id THEN @rownum WHEN @rowtotal := u.unit_id THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN  @rownum :=@rownum + 1 END) as rank FROM(SELECT yjfk.unit_id, un.name ,count(1)as count FROM `yjfk`  LEFT JOIN units as un ON un.id = yjfk.unit_id  WHERE  $ar $nt  GROUP BY yjfk.unit_id,un.name ORDER BY count DESC) AS u,(SELECT @rownum := 0 ,@rowtotal := NULL) r

	          	$re['rank'] =  DB::select("SELECT u.count,u.unit_id,u.name,(CASE WHEN @rowtotal = u.count THEN @rownum WHEN @rowtotal := u.count THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN  @rownum :=@rownum + 1 END) as rank FROM(SELECT yjfk.unit_id, un.name ,count(1)as count FROM `yjfk`  LEFT JOIN units as un ON un.id = yjfk.unit_id  WHERE  $ar $nt  GROUP BY yjfk.unit_id,un.name ORDER BY count DESC) AS u,(SELECT @rownum := 0 ,@rowtotal := NULL) r");
	   	 	}


	   	 	if($re){
	            $result = array('status' => '1000',  'message' => $re);
	            echo json_encode($result,TRUE);
	            exit();
	        }else{
	            $result = array('status' => '1001',  'message' => '暂无数据');
	            echo json_encode($result,TRUE);
	            exit();
	        }
	    }
    	  return view('admin.mjph.qiye');
    }



    public function leida($id,Request $request){

    	//获取警情上报数
    	$jq = DB::table('jqsb')->where('unit_id',$id)->count();
    	if (!$jq) {
    		$jq = '0';
    	}
    	//获取人员核查
    	$rysh = DB::table('rysh')->where('unit_id',$id)->count();
    	if (!$rysh) {
    		$rysh = '0';
    	}


    	//获取线索举报
    	$xs = DB::table('xiansuo')->where('unit_id',$id)->count();
		if (!$xs) {
    		$xs = '0';
    	}


    	//获取意见反馈
    	$yj = DB::table('yjfk')->where('unit_id',$id)->count();
    	if (!$yj) {
    		$yj = '0';
    	}

    	//获取企业信息完整度
    	//法人
    	$one = DB::table('faren')->where('unit_id',$id)->first();
    	if ($one) {
    		$on = '20';
    	}else{
    		$on = '0';
    	}

    	//保卫人员
    	$two = DB::table('baoweirenyuan')->where('unit_id',$id)->first();
    	if ($two) {
    		$tw = '20';
    	}else{
    		$tw = '0';
    	}

    	//重点岗位人员
    	$three = DB::table('zhongdianrenyuan')->where('unit_id',$id)->first();
    	if ($three) {
    		$thr = '20';
    	}else{
    		$thr = '0';
    	}

    	//重要部位
    	$four = DB::table('zhongyaobuwei')->where('unit_id',$id)->first();
    	if ($four) {
    		$fo = '20';
    	}else{
    		$fo = '0';
    	}
    	//基本信息
    	$five = DB::table('zhongyaobuwei')->where('id',$id)->first();
    	if (!$five) {
    		$a = '0'; 
    		$b = '0';  
    		$c = '0';	
    		$d = '0';	
    	}else{

	    	if ($five->name == '') {
	    		$a = '0';
	    	}else{
	    		$a = '5';
	    	}

	    	if ($five->city == '') {
	    		$b = '0';
	    	}else{
	    		$b = '5';
	    	}

	    	if ($five->area == '') {
	    		$c = '0';
	    	}else{
	    		$c = '5';
	    	}

	    	if ($five->org_id == '') {
	    		$d = '0';
	    	}else{
	    		$d = '5';
	    	}
    	}

    	$score = $on+$tw+$thr+$fo+$a+$b+$c+$d;

    	return view('admin.qyph.leida',['jq'=>$jq,'rysh'=>$rysh,'xs'=>$xs,'yj'=>$yj,'score'=>$score]);
    }




}